package tgc.edu.demo.jkxt.service;

import java.util.List;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import tgc.edu.demo.jkxt.custom.BaseService;
import tgc.edu.demo.jkxt.entity.Grade;
import tgc.edu.demo.jkxt.entity.Course;
import tgc.edu.demo.jkxt.repository.GradeRepository;
@Service
public class GradeService extends BaseService<Grade, Integer>{

	@Autowired
	private GradeRepository gradeDAO;
	@Resource
	private EntityManager entityManager;
	
	public List<Grade> findByStudentId(Integer id) {
		return gradeDAO.findByStudentId(id);
	}

	public List<Course> findCourseByTbClassId(Integer bjId) {
		
		return gradeDAO.findCourseByTbClassId(bjId);
	}

	public Grade findByStudentIdAndCourseId(Integer id, Integer kcId) {
		return gradeDAO.findByStudentIdAndCourseId(id,kcId);
		
	}

	public List<List<Object>> getCjb(Integer bjId, List<Course> courses) {
		String hql=" select t.student.stuNo,t.student.name " ;
		for(Course course : courses) {
			hql+=",	sum(case when t.course.id="+course. getId()+"then t.grade else 0 end)";
		}
			hql+=" ,sum(t.grade) as total";
			hql+=" from Grade t";
			hql+=" where t.student.tbClass.id=" +bjId;
			hql+=" group by t.student.id";
			hql+=" order by total desc";
			Query query=entityManager .createQuery(hql);
			entityManager.close();
			return query.getResultList();
		
		
		
	}

	public Grade findByShijuanIdAndStudentId(Integer shijuanId, Integer studentId) {
		return gradeDAO.findByShijuanIdAndStudentId(shijuanId,studentId);
	}
}
